<?php

/*
 * @author Ebrain Cedeno <ebraincedeno@gmail.com>
 */

abstract class ActiveRecord{
    
    public static $host;
    public static $port;
    public static $user;
    public static $passwd;
    public static $dbname;
    public static $type;
    public $Limit = '';
    public $Offset = '';
    public $fields = '*';
    public $labels = array();
    protected $rules = array();
    protected $filter='';
    protected $_columns = array();
    
    private $_conexion;
    private $_table_name;
    private $_relation = array();
    private $_pk;
    
    public function __construct($name=__CLASS__) {
        
        $this->_table_name = $name;
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                self::$port = (self::$port != "")?self::$port:3306;
                if (!$this->_conexion = mysql_connect(self::$host.":".self::$port,  self::$user, self::$passwd)){
                    throw new Exception("Error al intentar conectar al servidor de base de datos indicado ".  mysql_error());
                }
                else{
                    if (!mysql_select_db(self::$dbname)){
                        throw new Exception("Error al intentar seleccionar la base de datos indicada ".mysql_error());
                    }
                }
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                self::$port = (self::$port != "")?self::$port:5432;
                if (!$this->_conexion = pg_connect("host=".self::$host." port=".self::$port." dbname=".self::$dbname." user=".self::$user." password=".self::$passwd)){
                    throw new Exception("Error al intentar conectar a la base de datos indicada ".  pg_last_error());
                }
                
                break;
            
            default :
                throw new Exception("No se identifico el servidor de base de datos indicado");
                break;
            
        }
        
        //cargando las propiedades para el modelo
        
        $this->getColumnsByTable();
        
        //buscando cargando las relaciones de esta tabla
        
        $this->getFkByTable();
        
        //buscamos la clave primaria de la tabla
        
        $this->getPkByTable();
        
        
        
    }
    
    private function getColumnsByTable($table=null){
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                try{
                    $columns_table = (!is_null($table))?$table:$this->_table_name;
                    $sql = "SHOW COLUMNS FROM ".$columns_table;
                    $query = mysql_query($sql,  $this->_conexion);
                    
                    while ($resultado = mysql_fetch_assoc($query)){
                        
                        $this->$resultado['Field'] = "";
                        $this->_columns[] = $resultado['Field'];
                        if (!is_null($resultado['Default'])){
                            $this->$resultado['Field'] = $resultado['Default'];
                        }
                        else{
                            if (strtolower($resultado['Key']) != 'pri'){
                                $this->loadRules(array($resultado['Field']=>$resultado["Null"]));
                            }
                            $this->$resultado['Field'] = "";
                        }
                    }
                    
                
                }  catch (Exception $error){
                    die("Error al intentar cargar las propiedades para el modelo ".$this->_table_name." ".$error->getMessage());
                }
                
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                try{
                    
                    $sql = "SELECT column_name,is_nullable,column_default FROM information_schema.columns WHERE table_name='".$this->_table_name."' ORDER BY ordinal_position ASC";
                    $query = pg_query($this->_conexion,$sql);
                    while ($resultado = pg_fetch_assoc($query)){
                        $this->$resultado["column_name"] = "";
                        $this->_columns[] = $resultado["column_name"];
                        
                        if (strtolower($resultado['column_default']) != "" AND stripos("regclass",strtolower($resultado['column_default'])) === FALSE){
                            $this->$resultado['column_name'] = $resultado['column_default'];
                        }
                        else{
                            if (stripos("regclass",strtolower($resultado['column_default'])) === FALSE){
                                $this->loadRules(array($resultado['column_name']=>$resultado["is_nullable"]));
                            }
                            $this->$resultado['column_name'] = "";
                        }
                        
                    }
                
                }  catch (Exception $error){
                    
                    die("Error al intentar cargar las propiedades para el modelo ".$this->_table_name." ".$error->getMessage());
                    
                }
                
                break;
            
            default :
                
                break;
            
        }
    }
    
    private function loadRules($rules){
        
        if (strtolower($rules[$this->_columns[count($this->_columns)-1]]) == "no" and $this->_columns[count($this->_columns)-1] != $this->_pk ){
            $rules[$this->_columns[count($this->_columns)-1]] = "requested";
            $this->rules[] = $rules;
        }
        
    }
    
    private function getFkByTable(){
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                $sql = "SELECT REFERENCED_TABLE_NAME as REFERENCED,COLUMN_NAME,REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME='".$this->_table_name."'";
                $query = mysql_query($sql,$this->_conexion);
                
                while ($resultado = mysql_fetch_assoc($query)){
                    if (!is_null($resultado['REFERENCED'])){
                        //if ($contador == 0 ){
                        @$this->$resultado['REFERENCED'] = new $resultado['REFERENCED'];
                        $this->_relation[] = array($this->_table_name=>$resultado['COLUMN_NAME'],$resultado['REFERENCED']=>$resultado['REFERENCED_COLUMN_NAME']);
                        
                        //}
                    } 
                }
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                $sql = "SELECT
                            tc.constraint_name, tc.table_name, kcu.column_name as org_column_name, 
                            ccu.table_name AS foreign_table_name,
                            ccu.column_name AS foreign_column_name 
                        FROM 
                            information_schema.table_constraints AS tc 
                        JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
                        JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
                        WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='".$this->_table_name."';";
                
                $query = pg_query($this->_conexion,$sql);
                while ($resultado = pg_fetch_assoc($query)){
                    @$this->$resultado['foreign_table_name'] = new $resultado['foreign_table_name'];
                    $this->_relation[] = array($this->_table_name=>$resultado['org_column_name'],$resultado['foreign_table_name']=>$resultado['foreign_column_name']);
                }
                
                
                break;
            
        }
        
    }
    
    private  function getPkByTable(){
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                try{
                
                    $sql = "SHOW INDEX FROM ".$this->_table_name." WHERE UPPER(Key_name)='PRIMARY'";
                    $query = mysql_query($sql,$this->_conexion);
                    $resultado = mysql_fetch_assoc($query);
                    $this->_pk = $resultado['Column_name'];
                    
                }  catch (Exception $error){
                    
                    print "Se ha producido un error al intentar obtener el clave primaria ".$error->getMessage();
                    
                }
                break;
            
            case ConnectionType::POSTGRESQL:
                
                try{
                
                    $sql = "SELECT               
                                pg_attribute.attname as column_name, 
                                format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
                            FROM pg_index, 
                                    pg_class, 
                                    pg_attribute 
                            WHERE 
                                pg_class.oid = '".$this->_table_name."'::regclass AND
                                indrelid = pg_class.oid AND
                                pg_attribute.attrelid = pg_class.oid AND 
                                pg_attribute.attnum = any(pg_index.indkey)
                                AND indisprimary";
                    
                    $query = pg_query($this->_conexion,$sql);
                    $resultado = pg_fetch_assoc($query);
                    $this->_pk = $resultado['column_name'];
                    
                
                }  catch (Exception $error){
                    
                    print "Se ha producido un error al intentar obtener el clave primaria ".$error->getMessage();
                    
                }
                
                break;
            
        }
        
    }


    public function findAll($condition='',$param=array()){
        
        //preparando la condicion para el query
        
        if (count($param) >  0){
            foreach ($param as $field=>$field_value){
                
                $condition = str_replace($field, "'".$field_value."'", $condition);
            }
            
            if ($this->filter != ""){
                $this->filter = " AND ".$this->filter;
            }
            
            $WHERE = " WHERE ".$condition.$this->filter;
        }
        else{
            if ($this->filter != ""){
                $WHERE = $this->filter;
            }
            else{
                $WHERE = '';
            }
            
        }
        
        $estosDatos = array();
        $sql = "SELECT ".$this->fields." FROM ".$this->_table_name.$WHERE;
        
        //segun el manejador de base de datos seleccionamos el procedimiento
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                if ($this->Limit != '' && $this->Offset != ''){
                    $limit_offset = " LIMIT ".$this->Offset.",".$this->Limit;
                }else {
                    $limit_offset ='';
                }
                
                $query = mysql_query($sql.$limit_offset,  $this->_conexion);
                while ($resultado = mysql_fetch_assoc($query)){
                    $tmpClass = new $this->_table_name;
                    foreach ($resultado as $llave=>$value){
                        $tmpClass->$llave = $value;
                    }
                    
                    //buscamos el resultado de la relacion y los colocamos
                    
                    foreach ($this->_relation as $key=>$relation_value){
                        foreach ($relation_value as $key_tables=>$field_relation){
                            if ($key_tables != $this->_table_name){
                                
                                $sql_relation = "SELECT * FROM ".$key_tables." WHERE ".$field_relation."='".$tmpClass->$relation_value[$this->_table_name]."'"; 
                                $query_relation = mysql_query($sql_relation,$this->_conexion);
                                $resultado_relation = mysql_fetch_assoc($query_relation);
                                if (is_array($resultado_relation)){
                                    foreach ($resultado_relation as $llave=>$resultado_relation_value){
                                        $tmpClass->$key_tables->$llave = $resultado_relation_value;
                                    }
                                }
                            }
                        }
                    }
                    
                    $estosDatos[]=$tmpClass;
                }
                
                return $estosDatos;
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                if ($this->Limit != '' && $this->Offset != ''){
                    $limit_offset = " LIMIT ".$this->Limit." OFFSET ".$this->Offset;
                }else {
                    $limit_offset ='';
                }
                
                $query = pg_query($this->_conexion,$sql.$limit_offset);
                while ($resultado = pg_fetch_assoc($query)){
                    $tmpClass = new $this->_table_name;
                    foreach ($resultado as $llave=>$value){
                        $tmpClass->$llave = $value;
                    }
                    
                    //buscamos el resultado de la relacion y los colocamos
                    
                    foreach ($this->_relation as $key=>$relation_value){
                        foreach ($relation_value as $key_tables=>$field_relation){
                            if ($key_tables != $this->_table_name){
                                
                                $sql_relation = "SELECT * FROM ".$key_tables." WHERE ".$field_relation."='".$tmpClass->$relation_value[$this->_table_name]."'"; 
                                $query_relation = pg_query($this->_conexion,$sql_relation);
                                $resultado_relation = pg_fetch_assoc($query_relation);
                                foreach ($resultado_relation as $llave=>$resultado_relation_value){
                                    $tmpClass->$key_tables->$llave = $resultado_relation_value;
                                }
                                
                            }
                        }
                    }
                    
                    $estosDatos[]=$tmpClass;
                }
                
                return $estosDatos;
                
                break;
            
        }
        
    }
    
    public function find($condition='',$param=array()){
        
        //preparamos los parametros de la busqueda
        
        if (count($param) > 0){
            foreach ($param as $llave=>$value){
                $condition = str_replace($llave, "'".$value."'", $condition);
            }
            
            $WHERE = " WHERE ".$condition;
            
        }
        else{
            
            $WHERE = '';
            
        }
        
        $tmpClass = '';
        $sql = "SELECT ".$this->fields." FROM ".$this->_table_name.$WHERE;
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                if ($this->Limit != '' && $this->Offset != ''){
                    $limit_offset = " LIMIT ".$this->Offset.",".$this->Limit;
                }else {
                    $limit_offset ='';
                }
                
                
                $query = mysql_query($sql,  $this->_conexion);
                while ($resultado = mysql_fetch_assoc($query)){
                    $tmpClass = new $this->_table_name;
                    foreach ($resultado as $llave=>$value){
                        $tmpClass->$llave = $value;
                    }
                    
                    //buscamos el resultado de la relacion y los colocamos
                    
                    foreach ($this->_relation as $key=>$relation_value){
                        foreach ($relation_value as $key_tables=>$field_relation){
                            if ($key_tables != $this->_table_name){
                                
                                $sql_relation = "SELECT * FROM ".$key_tables." WHERE ".$field_relation."='".$tmpClass->$relation_value[$this->_table_name]."'"; 
                                $query_relation = mysql_query($sql_relation,$this->_conexion);
                                $resultado_relation = mysql_fetch_assoc($query_relation);
                                foreach ($resultado_relation as $llave=>$resultado_relation_value){
                                    $tmpClass->$key_tables->$llave = $resultado_relation_value;
                                }
                                
                            }
                        }
                    }
                    
                }
                
                return $tmpClass;
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                if ($this->Limit != '' && $this->Offset != ''){
                    $limit_offset = " LIMIT ".$this->Limit." OFFSET ".$this->Offset;
                }else {
                    $limit_offset ='';
                }
                
                
                $query = pg_query($this->_conexion,$sql);
                while ($resultado = pg_fetch_assoc($query)){
                    $tmpClass = new $this->_table_name;
                    foreach ($resultado as $llave=>$value){
                        $tmpClass->$llave = $value;
                    }
                    
                    //buscamos el resultado de la relacion y los colocamos
                    
                    foreach ($this->_relation as $key=>$relation_value){
                        foreach ($relation_value as $key_tables=>$field_relation){
                            if ($key_tables != $this->_table_name){
                                
                                $sql_relation = "SELECT * FROM ".$key_tables." WHERE ".$field_relation."='".$tmpClass->$relation_value[$this->_table_name]."'"; 
                                $query_relation = pg_query($this->_conexion,$sql_relation);
                                $resultado_relation = pg_fetch_assoc($query_relation);
                                foreach ($resultado_relation as $llave=>$resultado_relation_value){
                                    $tmpClass->$key_tables->$llave = $resultado_relation_value;
                                }
                                
                            }
                        }
                    }
                }
                
                return $tmpClass;
                
                break;
            
        }
        
    }
    
    public function findByPk($pk){
        
        try{
            $tmpClass = '';
            $sql = "SELECT ".$this->fields." FROM ".$this->_table_name." WHERE ".$this->_pk."='".$pk."'";
            
            switch (strtolower(self::$type)){
                
                case ConnectionType::MYSQL:
                    
                    $query = mysql_query($sql,  $this->_conexion);
                    while ($resultado = mysql_fetch_assoc($query)){
                        $tmpClass = new $this->_table_name;
                        foreach ($resultado as $llave=>$value){
                            $tmpClass->$llave = $value;
                        }
                    
                    //buscamos el resultado de la relacion y los colocamos
                    
                        foreach ($this->_relation as $key=>$relation_value){
                            foreach ($relation_value as $key_tables=>$field_relation){
                                if ($key_tables != $this->_table_name){
                                
                                    $sql_relation = "SELECT * FROM ".$key_tables." WHERE ".$field_relation."='".$tmpClass->$relation_value[$this->_table_name]."'"; 
                                    $query_relation = mysql_query($sql_relation,$this->_conexion);
                                    $resultado_relation = mysql_fetch_assoc($query_relation);
                                    foreach ($resultado_relation as $llave=>$resultado_relation_value){
                                        $tmpClass->$key_tables->$llave = $resultado_relation_value;
                                    }
                                
                                }
                            }
                        }
                    
                    }
                
                    return $tmpClass;
                    
                    break;
                
                case ConnectionType::POSTGRESQL:
                    
                    $query = pg_query($this->_conexion,$sql);
                    while ($resultado = pg_fetch_assoc($query)){
                        $tmpClass = new $this->_table_name;
                        foreach ($resultado as $llave=>$value){
                            $tmpClass->$llave = $value;
                        }
                    
                    //buscamos el resultado de la relacion y los colocamos
                    
                        foreach ($this->_relation as $key=>$relation_value){
                            foreach ($relation_value as $key_tables=>$field_relation){
                                if ($key_tables != $this->_table_name){
                                
                                    $sql_relation = "SELECT * FROM ".$key_tables." WHERE ".$field_relation."='".$tmpClass->$relation_value[$this->_table_name]."'"; 
                                    $query_relation = pg_query($this->_conexion,$sql_relation);
                                    $resultado_relation = pg_fetch_assoc($query_relation);
                                    foreach ($resultado_relation as $llave=>$resultado_relation_value){
                                        $tmpClass->$key_tables->$llave = $resultado_relation_value;
                                    }
                                
                                }
                            }
                        }
                    }
                
                    return $tmpClass;
                    
                    break;
                
            }
            
            
        }  catch (Exception $error){
            print "Error al realizar la consulta solicitada ".$error->getMessage();
        }
        
    }
    
    public function sql($sql,$param=array()){ // permite realizar una consulta personalizada sql
        
        if (count($param) > 0){
            foreach ($param as $llave=>$value){
                $sql = str_replace($llave, $value, $sql);
            }     
        }
       
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                $query = mysql_query($sql,  $this->_conexion);
                $estos_datos=array();
                while ($resultado = mysql_fetch_assoc($query)){
                    $estos_datos[] = $resultado;
                }
                
                return $estos_datos;
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                $query = pg_query($this->_conexion,$sql);
                $estos_datos=array();
                
                while ($resultado = pg_fetch_assoc($query)){
                    $estos_datos[] = $resultado;
                }
                
                return $estos_datos;
                
                break;
            
        }
        
    }
    
    public function search(){
        foreach ($this->_columns as $key=>$value){
            if ($value != $this->_pk){
                if ($this->$value !=""){
                    $this->filter .= "UPPER(".$value.")='".$this->$value."' AND ";
                }
            }
        }
        $this->filter = substr($this->filter,0,-4);
        
    }
    
    public function validate(){
        
        $valid = true;
        
        TForm::$ValueMessageError = array();
        foreach($this->rules as $llave=>$value){
            list($property,$validate)=each($value);
            
            if (property_exists($this, $property) and $property != $this->_pk){
                if (!array_key_exists($property, TForm::$ValueMessageError)){
                    if ($validate == "requested"){
                        if ($this->$property == ""){
                            $label = (array_key_exists($property, $this->labels))?$this->labels[$property]:$property;
                            TForm::$ValueMessageError[$property] = "El campo ".$label." es obligatorio";
                            $valid = false;
                        }
                    }
                    elseif (strtolower($validate) == "integer" Or strtolower($validate) == "numeric"){
                        if (!preg_match("/^[0-9]+$/", $this->$property)){
                            $label = (array_key_exists($property, $this->labels))?$this->labels[$property]:$property;
                            TForm::$ValueMessageError[$property] = "El campo ".$label." es solo numeros";
                            $valid = false;
                        }
                    }
                    elseif (strtolower($validate) == "email"){
                        if (!preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/", $this->$property)){
                            $label = (array_key_exists($property, $this->labels))?$this->labels[$property]:$property;
                            TForm::$ValueMessageError[$property] = "Ingrese un ".$label." valido";
                            $valid = false;
                        }
                    }
                    else{
                        if (method_exists($this, $validate)){
                            $valid = call_user_func(array($this,$validate));
                        }
                    }
                }
            }
        }
        
        return $valid;
    }
    
    public function save(){
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                try{
                    $sql = "INSERT INTO ".$this->_table_name." (";
                    $sql_data = "";
                    foreach ($this->_columns as $llave=>$field_name){
                        if (!empty($this->$field_name) and $field_name != $this->_pk){
                            $sql .= $field_name.",";
                            $sql_data .= "'".$this->$field_name."',";
                        }
                    }
                    $sql = substr($sql, 0,-1).") VALUES (".substr($sql_data,0,-1).");";
                    
                    if (!mysql_query($sql,$this->_conexion)){
                        return false;
                    }
                    else{
                        $primary_key = $this->_pk;
                        $this->$primary_key = mysql_insert_id();
                        return true;
                    }
                    
                }  catch (Exception $error){
                    print "Se ha producido un error al intentar guardar los datos";
                }
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                try{
                    $sql = "INSERT INTO ".$this->_table_name." (";
                    $sql_data = "";
                    foreach ($this->_columns as $llave=>$field_name){
                        if (!empty($this->$field_name)){
                            $sql .= $field_name.",";
                            $sql_data .= "'".$this->$field_name."',";
                        }
                    }
                    $sql = substr($sql, 0,-1).") VALUES (".substr($sql_data,0,-1).") returning ".$this->_pk.";";
                    if (!$insert_id = pg_query($this->_conexion,$sql)){
                        return false;
                    }
                    else{
                        $value = pg_fetch_row($$insert_id);
                        $primary_key = $this->_pk;
                        $this->$primary_key = $value[0];
                        return true;
                    }
                    
                }  catch (Exception $error){
                    print "Se ha producido un error al intentar guardar los datos";
                }
                
                break;
        }
        
    }
    
    public function update(){
        
        $sql = "UPDATE ".$this->_table_name." SET ";
        
        foreach ($this->_columns as $llave=>$field){
            $sql .= $field."='".$this->$field."', ";
        }
        $pk = $this->_pk;
        $sql = substr($sql,0,-2)." WHERE ".$pk."='".$this->$pk."';";
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                if (mysql_query($sql,  $this->_conexion)){
                    return true;
                }
                else{
                    return false;
                }
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                if (pg_query($this->_conexion,$sql)){
                    return true;
                }
                else{
                    return false;
                }
                
                break;
            
        }
        
    }
    
    public function delete(){
        
        $pk = $this->_pk;
        $sql = "DELETE FROM ".$this->_table_name." WHERE ".$pk."='".$this->$pk."'";
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
                if (mysql_query($sql,$this->_conexion)){
                    return true;
                }
                else{
                    return false;
                }
                
                break;
            
            case ConnectionType::POSTGRESQL:
                
                if (pg_query($this->_conexion,$sql)){
                    return true;
                }
                else {
                    return false;
                }
                
                break;
        }    
    }
    
    public function Count($condition="",$param=array()){
        
        if (count($param) > 0){
            foreach ($param as $llave=>$value){
                $condition = str_replace($llave, "'".$value."'", $condition);
            }
            
            $WHERE = " WHERE ".$condition;
            
        }
        else{
            
            $WHERE = '';
            
        }
        
        $sql = "SELECT COUNT(*) as total FROM ".$this->_table_name.$WHERE;
        
        switch (strtolower(self::$type)){
            
            case ConnectionType::MYSQL:
                
               $query = mysql_query($sql,$this->_conexion);
               $result = mysql_fetch_assoc($query);
               return $result['total']; 
               break;
            
            case ConnectionType::POSTGRESQL:
                
                $query = pg_query($this->_conexion,$sql);
                $result = pg_fetch_assoc($query);
                return $result['total'];
                break;
        }
        
    }
    
    public function setAttributes($attributes=array()){
        if (count($attributes) > 0){
            foreach($attributes as $attribute=>$value){
                if (property_exists($this, $attribute)){
                    $this->$attribute = $value;
                }
            }
        }
    }
}

class ConnectionType{
    CONST MYSQL = "mysql";
    CONST POSTGRESQL = "postgresql";
}
?>